package com.mxpio.erp.equipment.service.impl;

import com.mxpio.erp.common.equipment.entity.EquipmentStockCkApplyItem;
import com.mxpio.erp.common.equipment.service.EquipmentStockCkApplyItemService;
import com.mxpio.erp.common.equipment.vo.VEquipmentStockInventory;
import com.mxpioframework.jpa.JpaUtil;
import com.mxpioframework.jpa.query.Criteria;
import com.mxpioframework.jpa.query.Operator;
import com.mxpioframework.system.service.impl.BaseServiceImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @Description: tpm_stock_ck_apply_item
 * @Author: jeecg-boot
 * @Date:   2021-10-14
 * @Version: V1.0
 */
@Service
public class EquipmentStockCkApplyItemServiceImpl extends BaseServiceImpl<EquipmentStockCkApplyItem> implements EquipmentStockCkApplyItemService {

	@Override
	public List<EquipmentStockCkApplyItem> selectByMainId(String mainId) {
        Criteria c = Criteria.create().addCriterion("ckApplyId", Operator.EQ, mainId);
        return list(EquipmentStockCkApplyItem.class,c);
	}

    @Override
    public Page<EquipmentStockCkApplyItem> queryByMainId(Pageable page, String mainId) {
        Criteria c = Criteria.create().addCriterion("ckApplyId", Operator.EQ, mainId);
        return JpaUtil.linq(EquipmentStockCkApplyItem.class).where(c).paging(page);
    }

	@Override
    @Transactional
	public boolean saveItem(EquipmentStockCkApplyItem tpmStockckApplyItem) {
		tpmStockckApplyItem.setIsDel(0);
		tpmStockckApplyItem.setVersion(0);
		tpmStockckApplyItem.setStatus(0);
		super.save(tpmStockckApplyItem);
		return true;
	}

	@Override
    @Transactional
	public boolean updateItemById(EquipmentStockCkApplyItem tpmStockckApplyItem) {
		tpmStockckApplyItem.setVersion(tpmStockckApplyItem.getVersion()+1);
		super.update(tpmStockckApplyItem);
		return true;
	}

	@Override
	public Page<EquipmentStockCkApplyItem> queryCkPostingByMainId(Pageable page, String mainId) {
        String sql = "SELECT\n" +
                "\t\t\tcai.*,\n" +
                "\t\t\ttm.NAME AS materialsName,\n" +
                "\t\t\ttm.CODE AS materialsCode,\n" +
                "\t\t\ttm.product_name AS materialsType,\n" +
                "\t\t\ttm.brand AS materialsBrand,\n" +
                "\t\t\ttm.spec AS materialsSpec,\n" +
                "\t\t\ttm.stock_unit AS materialsUnit,\n" +
                "\t\t\ttm.es_price AS materialsEsPrice,\n" +
                "\t\t\tsum(ti.current_onhand_qty) as stockNum,\n" +
                "\t\t\tIFNULL(tmp.already_out_number,0) AS alreadyOutNumber\n" +
                "\t\tFROM\n" +
                "\t\t\tMB_ERP_EQUIPMENT_STOCK_CK_APPLY_ITEM cai\n" +
                "\t\t\tLEFT JOIN MB_ERP_EQUIPMENT_MATERIAL tm ON tm.id = cai.materials_id\n" +
                "\t\t\tleft join MB_ERP_EQUIPMENT_STOCK_ITEM_INVENTORY ti on ti.materials_id = cai.materials_id\n" +
                "\t\t\tLEFT JOIN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tch.ck_apply_item_id,\n" +
                "\t\t\t\t\tsum(ch.current_out_number) AS already_out_number\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tMB_ERP_EQUIPMENT_STOCK_CK_HISTORY ch\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tch.ck_apply_id = \n" + mainId +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tch.ck_apply_item_id\n" +
                "\t\t\t\t\t) tmp ON cai.id = tmp.ck_apply_item_id\n" +
                "\t\tWHERE\n" +
                "\t\t\tcai.ck_apply_id = \n" + mainId +
                "\t\t\tand cai.status = 1\n" +
                "\t\t\tgroup by cai.id ";
        List resultList = JpaUtil.nativeQuery(sql, EquipmentStockCkApplyItem.class).getResultList();
        String countSql = sql + " limit " + (page.getPageNumber()-1)*page.getPageSize() + ","+page.getOffset();
        List total = JpaUtil.nativeQuery(countSql).getResultList();
        return new PageImpl<EquipmentStockCkApplyItem>(resultList, page, total.size());
	}

	@Override
	public Page<VEquipmentStockInventory> queryPageListInventory(Pageable page, String materialsId) {
	    String sql = "SELECT\n" +
                "\t\t\tti.*,\n" +
                "\t\t\ttm.NAME AS materialsName,\n" +
                "\t\t\ttm.CODE AS materialsCode,\n" +
                "\t\t\ttm.product_name AS materialsType,\n" +
                "\t\t\ttm.brand AS materialsBrand,\n" +
                "\t\t\ttm.spec AS materialsSpec,\n" +
                "\t\t\ttm.stock_unit AS materialsUnit,\n" +
                "\t\t\ttm.es_price AS materialsEsPrice,\n" +
                "\t\t\tsum( ti.current_onhand_qty ) AS stockNum,\n" +
                "\t\t\trai.real_price\n" +
                "\t\tFROM\n" +
                "\t\t\tMB_ERP_EQUIPMENT_STOCK_ITEM_INVENTORY ti\n" +
                "\t\t\tLEFT JOIN MB_ERP_EQUIPMENT_MATERIAL tm ON tm.id = ti.materials_id\n" +
                "\t\t\tLEFT JOIN MB_ERP_EQUIPMENT_STOCK_RK_APPLY_ITEM rai ON rai.id = ti.inbound_apply_item_id\n" +
                "\t\tWHERE\n" +
                "\t\t\tti.materials_id =  " + materialsId ;

        List resultList = JpaUtil.nativeQuery(sql, VEquipmentStockInventory.class).getResultList();
        String countSql = sql + " limit "  + (page.getPageNumber()-1)*page.getPageSize() + ","+page.getOffset();
        List total = JpaUtil.nativeQuery(countSql).getResultList();
        return new PageImpl<VEquipmentStockInventory>(resultList, page, total.size());
	}
}
